﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Bing.Base;
using System.Data.OracleClient;

namespace Bing.DataAccess
{
    class Oracle : IDbHelper
    {
        string connstr = "";

        public Oracle(string conn)
        {
            connstr = conn;
        }

        public void Add(BingSession sessinfo, ref IEntity entity)
        {
            ExecSql(null, GetAddSql(entity));
        }

        public void AddList(BingSession sessinfo, ref List<IEntity> entityList)
        {
            string[] sqllist = new string[entityList.Count];
            for (int i = 0; i < entityList.Count; i++)
            {
                sqllist[i] = GetAddSql(entityList[i]);
            }
            ExecSql(null, sqllist);
        }

        public void Update(BingSession sessinfo, ref IEntity entity)
        {
            ExecSql(null, GetUpdateSql(entity));
        }

        public void UpdateList(BingSession sessinfo, ref List<IEntity> entityList)
        {
            string[] sqllist = new string[entityList.Count];
            for (int i = 0; i < entityList.Count; i++)
            {
                sqllist[i] = GetUpdateSql(entityList[i]);
            }
            ExecSql(null, sqllist);
        }

        public void DelByPrimaryKey(BingSession sessinfo, params IEntity[] entityList)
        {
            string[] sqllist = new string[entityList.Length];
            for (int i = 0; i < entityList.Length; i++)
            {
                IEntity e = entityList[i];
                sqllist[i] = "delete from  " + e.GetTableName() + " where " +
                    e.GetPrimaryKey() + "='" + e.GetValue(e.GetPrimaryKey()).ToString() + "'";
            }
            ExecSql(null, sqllist);
        }

        public void GetByPrimaryKey(ref IEntity entity)
        {
            string sql = "select * from " + entity.GetTableName() + " where " +
                entity.GetPrimaryKey() + "='" + entity.GetValue(entity.GetPrimaryKey()).ToString() + "'";
            DataSet ds = ExecSqlQuery(null, sql);
            entity.FromDataRow(ds.Tables[0].Rows[0]);
        }

        public void GetByPrimaryKeyList(ref List<IEntity> entityList)
        {
            string[] sqllist = new string[entityList.Count];
            for (int i = 0; i < entityList.Count; i++)
            {
                IEntity e = entityList[i];
                sqllist[i] = "select * from " + e.GetTableName() + " where " +
                    e.GetPrimaryKey() + "='" + e.GetValue(e.GetPrimaryKey()).ToString() + "'";
            }
            DataSet ds = ExecSqlQuery(null, sqllist);
            for (int i = 0; i < entityList.Count; i++)
            {
                IEntity e = entityList[i];
                e.FromDataRow(ds.Tables[i].Rows[0]);
            }
        }

        public System.Data.DataSet GetList(string strWhere, IEntity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM " + entity.GetTableName());
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return ExecSqlQuery(null, strSql.ToString());
        }

        public System.Data.DataSet GetListTop(int Top, string strWhere, string filedOrder, IEntity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from ( select ");
            strSql.Append(" t.*,rownum rnm FROM " + entity.GetTableName() + " t ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            if (!string.IsNullOrEmpty(filedOrder))
            {
                strSql.Append("order by " + filedOrder);
            }
            else
            {
                strSql.Append("order by " + entity.GetPrimaryKey() + " desc");
            }
            strSql.Append(" ) where rnm<="+Top.ToString());
            return ExecSqlQuery(null, strSql.ToString());
        }

        public int GetRecordCount(string strWhere, IEntity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM " + entity.GetTableName());
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet obj = ExecSqlQuery(null, strSql.ToString());
            return Convert.ToInt32(obj.Tables[0].Rows[0][0]);
        }

        public System.Data.DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, IEntity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T."+entity.GetPrimaryKey()+" desc");
            }
            strSql.Append(")AS Row, T.*  from "+entity.GetTableName()+" T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return ExecSqlQuery(null,strSql.ToString());
        }

        public System.Data.DataSet ExecSqlQuery(BingSession sessinfo, params string[] strSql)
        {
            if (strSql != null && strSql.Length > 0)
            {
                System.Diagnostics.Stopwatch sw = DbFactory.StartDate();
                string logMsg = "";
                DataSet ds = new DataSet();
                using (OracleConnection oracleConnection = new OracleConnection(connstr))
                {
                    if (oracleConnection != null)
                    {
                        if (oracleConnection.State == System.Data.ConnectionState.Closed)
                            oracleConnection.Open();

                        foreach (string sql in strSql)
                        {
                            using (OracleDataAdapter da = new OracleDataAdapter(sql, oracleConnection))
                            {
                                string TableName = "Table" + (ds.Tables.Count == 0 ? "" : ds.Tables.Count.ToString());
                                DataTable dt = new DataTable(TableName);
                                da.Fill(dt);
                                ds.Tables.Add(dt);
                            }
                            logMsg += sql;
                        }
                        oracleConnection.Close();
                    }
                }
                DbFactory.StopDate(sessinfo, logMsg, sw);
                return ds;
            }
            return null;
        }

        public List<int> ExecSql(BingSession sessinfo, params string[] strSql)
        {
            if (strSql != null && strSql.Length > 0)
            {
                System.Diagnostics.Stopwatch sw = DbFactory.StartDate();
                string logMsg = "";
                using (OracleConnection oracleConnection = new OracleConnection(connstr))
                {
                    if (oracleConnection != null)
                    {
                        if (oracleConnection.State == System.Data.ConnectionState.Closed)
                            oracleConnection.Open();

                        OracleCommand oracleCommand = oracleConnection.CreateCommand();

                        OracleTransaction oracleTransaction = oracleConnection.BeginTransaction();
                        oracleCommand.Transaction = oracleTransaction;
                        List<int> ResCount = new List<int>();
                        try
                        {
                            foreach (string sql in strSql)
                            {
                                oracleCommand.CommandText = sql;
                                ResCount.Add(oracleCommand.ExecuteNonQuery());
                                logMsg += sql;
                            }
                            oracleTransaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            oracleTransaction.Rollback();

                            BingBase.WriteErrorLog("ExecSql", ex);
                        }
                        oracleConnection.Close();
                        return ResCount;
                    }
                }
                DbFactory.StopDate(sessinfo, logMsg, sw);
            }
            return null;
        }

        public System.Data.DataSet ExecProc(BingSession sess, string procName, params BingDbParams[] parameters)
        {
            if (!string.IsNullOrEmpty(procName))
            {
                System.Diagnostics.Stopwatch sw = DbFactory.StartDate();
                string logMsg = "存储过程：" + procName;
                DataSet ds = new DataSet();
                using (OracleConnection oracleConnection = new OracleConnection(connstr))
                {
                    if (oracleConnection != null)
                    {
                        if (oracleConnection.State == System.Data.ConnectionState.Closed)
                            oracleConnection.Open();
                        OracleCommand oracleCommand = oracleConnection.CreateCommand();
                        oracleCommand.CommandType = CommandType.StoredProcedure;
                        oracleCommand.CommandText = procName;
                        if (parameters != null)
                        {
                            for (int i = 0; i < parameters.Length; i++)
                            {
                                //oracleCommand.Parameters.Add((OracleParameter)parameters[i]);
                                //logMsg += parameters[i].ParameterName + " ：" + parameters[i].Value == null ? "" : parameters[i].Value.ToString();
                            }
                        }
                        using (OracleDataAdapter da = new OracleDataAdapter(oracleCommand))
                        {
                            da.Fill(ds);
                        }
                        oracleConnection.Close();
                    }

                }
                DbFactory.StopDate(sess, logMsg, sw);

                return ds;
            }
            return null;
        }

        public string GetAddSql(IEntity entity)
        {
            entity.SetValue(entity.GetPrimaryKey(), BingBase.GetId());
            string strSql = "insert into " + entity.GetTableName() + " values( ";
            foreach (string str in entity.GetAllColumn())
            {
                if (entity.GetColumnIsDB(str))
                {
                    object values = entity.GetValue(str);
                    if (str.EndsWith("_ObjType"))
                    {
                        if (values == null || string.IsNullOrEmpty(values.ToString()))
                        {
                            entity.SetValue(str, BingBase.GetSessinfo().Dbsign);
                        }
                    }
                    else if (str.EndsWith("_CreateDate") || str.EndsWith("_EditDate"))
                    {
                        if (values == null || ((DateTime)values) == DateTime.MinValue)
                        {
                            entity.SetValue(str, DateTime.Now);
                        }
                    }
                    else if (str.EndsWith("_Creator") || str.EndsWith("_Editor"))
                    {
                        if (values == null || string.IsNullOrEmpty(values.ToString()))
                        {
                            entity.SetValue(str, BingBase.GetSessinfo().UserId);
                        }
                    }
                    if (values is decimal)
                    {
                        strSql += values.ToString() + ",";
                    }
                    else if (values is DateTime)
                    {
                        strSql += "to_date('" + values.ToString() + "','yyyy-mm-dd hh24:mi:ss'),";
                    }
                    else
                    {
                        strSql += "'" + values.ToString().Replace("'", "") + "',";
                    }
                }
            }
            return strSql.Trim(',') + ")";
        }

        public string GetUpdateSql(IEntity entity)
        {
            string strSql = "update " + entity.GetTableName() + " set ";
            foreach (string str in entity.GetAllColumn())
            {
                if (entity.GetColumnIsDB(str))
                {
                    if (str != entity.GetPrimaryKey())
                    {
                        object values = entity.GetValue(str);
                        if (values is decimal)
                        {
                            strSql += str + "=" + values.ToString() + ",";
                        }
                        else if (values is DateTime)
                        {
                            strSql += str + "=to_date('" + values.ToString() + "','yyyy-mm-dd hh24:mi:ss'),";
                        }
                        else
                        {
                            strSql += str + "='" + values.ToString().Replace("'","") + "',";
                        }
                    }
                }
            }
            strSql = strSql.Trim(',') + " where " + entity.GetPrimaryKey() + "='" + entity.GetValue(entity.GetPrimaryKey()) + "'";
            return strSql;
        }

        public BingDbType GetDBType()
        {
            return BingDbType.Oracle;
        }

        public void AddOrEditByte(IEntity entity, string colName, object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByte(IEntity entity, string colName)
        {
            throw new NotImplementedException();
        }

        public void AddOrEditFile(IEntity entity, string colName, string fileName)
        {
            throw new NotImplementedException();
        }

        public string GetFile(IEntity entity, string colName)
        {
            throw new NotImplementedException();
        }

        #region IDbHelper 成员

        public void AddOrEditByte(BingSession sess, string tableName, string colName, object obj)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void AddOrEditFile(BingSession sess, string tableName, string colName, string fileName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void AddOrUpdate(BingSession sessinfo, ref IEntity entity)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void AddOrUpdateList(BingSession sessinfo, ref List<IEntity> entityList)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void DelByPrimaryKey(BingSession sessinfo, params string[] primarykeys)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public string GetAddSql(ref IEntity entity)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public IEntity GetByPrimaryKey(string primarykeys)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public List<IEntity> GetByPrimaryKeyList(params string[] primarykeys)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public object GetByte(string tableName, string colName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public string GetFile(string tableName, string colName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public DataSet GetList(string strWhere, string filedOrder, string table)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, string table)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public DataSet GetListTop(int Top, string strWhere, string filedOrder, string table)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public int GetRecordCount(string strWhere, string table)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void Update(BingSession sessinfo, params IEntity[] entityList)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion
    }
}
